﻿package com.carorderdetail.model;

import java.util.*;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import com.carstop.model.*;
import com.carorder.model.*;
import com.member.model.*;


public class CarOrderDetailDAO implements CarOrderDetailVO_interface {
	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/CARPOOL");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT_STMT = 
		"INSERT INTO CAR_ORDER_DETAIL ( order_detail_id, order_id, rider_path_id, driver_path_id, create_time, fee, member_id, car_pool_type, status) VALUES (?, ?, ?, ?, SYSDATE, ?, ?, ?, '0')";
	private static final String GET_ALL_STMT = 
		"SELECT order_detail_id, order_id, rider_path_id, driver_path_id, create_time, close_time, status, fee, member_id, car_pool_type FROM CAR_ORDER_DETAIL ORDER BY rider_path_id"; 
	private static final String GET_ONE_STMT = 
		"SELECT rider_path_id, driver_path_id, create_time, close_time, status, fee, member_id, car_pool_type FROM CAR_ORDER_DETAIL WHERE rider_path_id = ?"; 
	private static final String DELETE = 
		"DELETE FROM CAR_ORDER_DETAIL WHERE rider_path_id = ?";
	private static final String UPDATE = 
		"UPDATE CAR_ORDER_DETAIL SET fee = ?, status = ? WHERE rider_path_id = ?";
	private static final String FIND_ORDER = 
			"SELECT rider_path_id , driver_path_id, create_time, close_time, status, rider_path_id, fee,car_pool_type from CAR_ORDER_DETAIL WHERE member_id =?";
	public static final String GET_RIDER_PATH_ID = "SELECT driver_path_id,RIDER_PATH_ID,member_id, order_id,fee FROM CAR_ORDER_DETAIL WHERE MEMBER_ID=?  and status=0";
	public static final String UPDATE_STATUS = "UPDATE car_order_detail SET status='1' WHERE driver_path_id=? AND member_id=? and status=0";
	public static final String GET_ONE_ORD = "SELECT * FROM car_order_detail WHERE order_id=? AND member_id=?";

	private static final String UPDATE_DRIVER_SCORES = 
		"UPDATE CAR_ORDER_DETAIL SET driver_scores = ? WHERE order_id = ? AND member_id = ?";

	private static final String UPDATE_RIDER_SCORES = 
		"UPDATE CAR_ORDER_DETAIL SET rider_scores = ? WHERE order_id = ? AND member_id = ?";

	private static final String GET_ONE_ORDERID = 
		"SELECT order_detail_id, order_id, create_time, close_time, status, rider_path_id, fee, member_id, car_pool_type,driver_scores,rider_scores FROM CAR_ORDER_DETAIL WHERE order_id = ?"; 
		
	@Override
	public void insert(CarOrderDetailVO carOrderDetailVO, Connection con) {
		
		PreparedStatement pstmt = null;
		String driverPathId = carOrderDetailVO.getDriverPathId();
		String riderPathId = carOrderDetailVO.getRiderPathId();

		try {
            
			if (con == null) {
				System.out.println("CarOrderDetail -- A new connection is ceated");
				con = ds.getConnection();
			}
			
			/*
			 * insert CarStopDetail and CarStopPassenger
			 */
			CarStopDetailDAO carstopDetailDAO = new CarStopDetailDAO();
			carstopDetailDAO.insertByCarOrderDetailVO(con, carOrderDetailVO);
			
			/*
			 * insert CarOrderDetail
			 */
			pstmt = con.prepareStatement(INSERT_STMT);
			
			pstmt.setString(1, carOrderDetailVO.getRiderPathId());
			pstmt.setString(2, carOrderDetailVO.getDriverPathId());
			pstmt.setString(3, carOrderDetailVO.getRiderPathId());
			pstmt.setString(4, carOrderDetailVO.getDriverPathId());
			pstmt.setInt(5, carOrderDetailVO.getFee());
			pstmt.setString(6, carOrderDetailVO.getMemberId());
			pstmt.setString(7, carOrderDetailVO.getCarPoolType());

			pstmt.executeUpdate();
    		System.out.println(driverPathId + " 新增訂單明細編號" + riderPathId );

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
		}
	}

	@Override
	public void update(CarOrderDetailVO carOrderDetailVO) {
		
		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(UPDATE);

			pstmt.setInt(1, carOrderDetailVO.getFee());
			pstmt.setString(2, carOrderDetailVO.getStatus());
			pstmt.setString(3, carOrderDetailVO.getRiderPathId());

			pstmt.executeUpdate();

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		
	}
	
	@Override
	public void update_driver_scores(CarOrderDetailVO carOrderDetailVO) {
		
		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(UPDATE_DRIVER_SCORES);

			pstmt.setInt(1, carOrderDetailVO.getDriver_scores());
			pstmt.setString(2, carOrderDetailVO.getOrderId());
			pstmt.setString(3, carOrderDetailVO.getMemberId());

			pstmt.executeUpdate();

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		
	}
	
	@Override
	public void update_rider_scores(CarOrderDetailVO carOrderDetailVO) {
		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(UPDATE_RIDER_SCORES);

			pstmt.setInt(1, carOrderDetailVO.getRider_scores());
			pstmt.setString(2, carOrderDetailVO.getOrderId());
			pstmt.setString(3, carOrderDetailVO.getMemberId());

			pstmt.executeUpdate();

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}				
	}

	@Override
	public void delete(String orderDetailId) {
		
		Connection con = null;
		PreparedStatement pstmt = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(DELETE);
			
			pstmt.setString(1, orderDetailId);
			
			pstmt.executeUpdate();

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		
	}
	
	@Override
	public CarOrderDetailVO getOneOrderId(String orderId) {

		CarOrderDetailVO carOrderDetailVO = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ONE_ORDERID);
			
			pstmt.setString(1, orderId);
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				// empVo 也稱為 Domain objects
				carOrderDetailVO = new CarOrderDetailVO();
				carOrderDetailVO.setOrderDetailId(rs.getString("order_detail_id"));
				carOrderDetailVO.setOrderId(rs.getString("order_id"));
				carOrderDetailVO.setCreateTime(rs.getTimestamp("create_time"));
				carOrderDetailVO.setCloseTime(rs.getTimestamp("close_time"));
				carOrderDetailVO.setStatus(rs.getString("status"));
				carOrderDetailVO.setRiderPathId(rs.getString("rider_path_id"));
				carOrderDetailVO.setFee(rs.getInt("fee"));
				carOrderDetailVO.setMemberId(rs.getString("member_id"));
				carOrderDetailVO.setCarPoolType(rs.getString("car_pool_type"));
				carOrderDetailVO.setDriver_scores(rs.getInt("driver_scores"));
				carOrderDetailVO.setRider_scores(rs.getInt("rider_scores"));
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return carOrderDetailVO;
	}

	@Override
	public CarOrderDetailVO findByPrimaryKey(String riderPathId) {

		CarOrderDetailVO carOrderDetailVO = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ONE_STMT);
			
			pstmt.setString(1, riderPathId);
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				carOrderDetailVO = new CarOrderDetailVO();
				carOrderDetailVO.setCreateTime(rs.getTimestamp("create_time"));
				carOrderDetailVO.setCloseTime(rs.getTimestamp("close_time"));
				carOrderDetailVO.setStatus(rs.getString("status"));
				carOrderDetailVO.setRiderPathId(rs.getString("rider_path_id"));
				carOrderDetailVO.setOrderDetailId(rs.getString("rider_path_id"));
				carOrderDetailVO.setDriverPathId(rs.getString("driver_path_id"));
				carOrderDetailVO.setOrderId(rs.getString("rider_path_id"));
				carOrderDetailVO.setFee(rs.getInt("fee"));
				carOrderDetailVO.setMemberId(rs.getString("member_id"));
				carOrderDetailVO.setCarPoolType(rs.getString("car_pool_type"));
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return carOrderDetailVO;
	}

	@Override
	public List<CarOrderDetailVO> getAll() {
		List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
		CarOrderDetailVO carOrderDetailVO = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			con = ds.getConnection();
			pstmt = con.prepareStatement(GET_ALL_STMT);
			rs = pstmt.executeQuery();

			while (rs.next()) {
				// empVO �]�٬� Domain objects
				carOrderDetailVO = new CarOrderDetailVO();
				carOrderDetailVO.setOrderDetailId(rs.getString("rider_path_id"));
				carOrderDetailVO.setOrderId(rs.getString("driver_path_id"));
				carOrderDetailVO.setRiderPathId(rs.getString("rider_path_id"));
				carOrderDetailVO.setDriverPathId(rs.getString("driver_path_id"));
				carOrderDetailVO.setCreateTime(rs.getTimestamp("create_time"));
				carOrderDetailVO.setCloseTime(rs.getTimestamp("close_time"));
				carOrderDetailVO.setStatus(rs.getString("status"));
				carOrderDetailVO.setFee(rs.getInt("fee"));
				carOrderDetailVO.setMemberId(rs.getString("member_id"));
				carOrderDetailVO.setCarPoolType(rs.getString("car_pool_type"));
				list.add(carOrderDetailVO); // Store the row in the vector
			}

			// Handle any driver errors
		} catch (SQLException se) {
			throw new RuntimeException("A database error occured. "
					+ se.getMessage());
			// Clean up JDBC resources
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (con != null) {
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
		}
		return list;
	}
	//android找尋此會員的CarOrderDetail
		////////////////////////////////////////////////////////////
		public List<CarOrderDetailVO> findorder(String member_id){
			List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
			CarOrderDetailVO carOrderDetailVO = null;
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;

			try {

				con = ds.getConnection();
				pstmt = con.prepareStatement(FIND_ORDER);
				
				pstmt.setString(1, member_id);
				
				rs = pstmt.executeQuery();

				while (rs.next()) {
					carOrderDetailVO = new CarOrderDetailVO();				
					carOrderDetailVO.setRiderPathId(rs.getString("rider_path_id"));
					carOrderDetailVO.setDriverPathId(rs.getString("driver_path_id"));
					carOrderDetailVO.setOrderDetailId(rs.getString("rider_path_id"));
					carOrderDetailVO.setOrderId(rs.getString("driver_path_id"));
					carOrderDetailVO.setCreateTime(rs.getTimestamp("create_time"));
					carOrderDetailVO.setCloseTime(rs.getTimestamp("close_time"));
					carOrderDetailVO.setStatus(rs.getString("status"));
					carOrderDetailVO.setFee(rs.getInt("fee"));
					carOrderDetailVO.setCarPoolType(rs.getString("car_pool_type"));
					list.add(carOrderDetailVO);
				}

				// Handle any driver errors
			} catch (SQLException se) {
				throw new RuntimeException("A database error occured. "
						+ se.getMessage());
				// Clean up JDBC resources
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}		
			
			
			return list;
			
		}
		
		//anroid用會員ID取得rider_path_id
		////////////////////////////////////////////
		public List<CarOrderDetailVO> getRider_path_id(String member_id) {
			String rider_path_id=null;
			List<CarOrderDetailVO> list = new ArrayList<CarOrderDetailVO>();
			CarOrderDetailVO carorderdetailVO = null;
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				conn = ds.getConnection();
				pstmt = conn.prepareStatement(GET_RIDER_PATH_ID);
				pstmt.setString(1, member_id);

				rs = pstmt.executeQuery();
				while (rs.next()) {
					carorderdetailVO =new CarOrderDetailVO();				
					carorderdetailVO.setRiderPathId(rs.getString("rider_path_id"));
					carorderdetailVO.setMemberId(rs.getString("member_id"));
					carorderdetailVO.setOrderId(rs.getString("order_id"));
					carorderdetailVO.setDriverPathId(rs.getString("driver_path_id"));
					carorderdetailVO.setFee(rs.getInt("fee"));
					list.add(carorderdetailVO);
				}
			} catch (SQLException e) {
				throw new RuntimeException("A database error occured. "
						+ e.getMessage());
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
			return list;
		}
		
		public CarOrderDetailVO getById(String order_id, String member_id) {		
			CarOrderDetailVO codVO = null;		
			Connection conn = null;		
			PreparedStatement pstmt = null;		
			ResultSet rs = null;		
		try {		
			conn = ds.getConnection();		
			pstmt = conn.prepareStatement(GET_ONE_ORD);		
			pstmt.setString(1, order_id);		
			pstmt.setString(2, member_id);		
				rs = pstmt.executeQuery();		
				while (rs.next()) {		
					codVO = new CarOrderDetailVO();		
					codVO.setFee(rs.getInt("fee"));		
					codVO.setMemberId(rs.getString("member_id"));		
				}		
			} catch (SQLException e) {		
				throw new RuntimeException("A database error occured. "		
						+ e.getMessage());		
			} finally {		
				if (rs != null) {		
					try {		
						rs.close();		
					} catch (SQLException e) {		
						e.printStackTrace(System.err);		
					}		
				}		
				if (pstmt != null) {		
					try {		
						pstmt.close();		
					} catch (SQLException e) {		
						e.printStackTrace(System.err);		
					}		
				}		
				if (conn != null) {		
					try {		
						conn.close();		
					} catch (SQLException e) {		
						e.printStackTrace(System.err);		
					}		
				}		
			}		
		
			return codVO;		
		}		
		
		public void updateStatus(String driverPathId, String memberId) {		
			PreparedStatement pstmt = null;		
			Connection conn = null;		
			try {		
				conn = ds.getConnection();		
				pstmt = conn.prepareStatement(UPDATE_STATUS);		
				conn.setAutoCommit(false);		
				pstmt.setString(1, driverPathId);		
				pstmt.setString(2, memberId);		
		
				pstmt.executeUpdate();		
		
				CarOrderDetailVO codVO = getById(driverPathId, memberId);		
		
				MemberDAO dao = new MemberDAO();		
				MemberVO memberVO = dao.findByMemberId(memberId);		
				dao.updateBalance(memberVO, null, codVO, conn);		
		
		
				CarOrderDAO dao1 = new CarOrderDAO();		
				CarOrderVO coVO = dao1.getByOrderId(driverPathId);		
				MemberVO memVO = dao.findByMemberId(coVO.getMemberId());		
				dao.updateBalance(memVO, null, codVO.getFee(), conn);		
		
				conn.commit();		
				conn.setAutoCommit(true);		
			} catch (SQLException e) {		
				if (conn != null) {		
					try {		
						conn.rollback();		
					} catch (SQLException excep) {		
						throw new RuntimeException("rollback error occured. "		
								+ excep.getMessage());		
					}		
				}		
				throw new RuntimeException("A database error occured. "		
						+ e.getMessage());		
			} finally {		
				if (pstmt != null) {		
					try {		
						pstmt.close();		
					} catch (SQLException e) {		
						e.printStackTrace(System.err);		
					}		
				}		
				if (conn != null) {		
					try {		
						conn.close();		
					} catch (SQLException e) {		
						e.printStackTrace(System.err);		
					}		
				}		
			}		
		}		

	public void add_driver_scores(String order_id, String member_id,String driver_scores) {
			
			Connection con = null;
			PreparedStatement pstmt = null;

			try {

				con = ds.getConnection();
				pstmt = con.prepareStatement(UPDATE_DRIVER_SCORES);
				pstmt.setString(1, driver_scores);
				pstmt.setString(2, order_id);
				pstmt.setString(3, member_id);
				

				pstmt.executeUpdate();

				// Handle any driver errors
			} catch (SQLException se) {
				throw new RuntimeException("A database error occured. "
						+ se.getMessage());
				// Clean up JDBC resources
			} finally {
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
			
		}
	
		@Override
		public CarOrderDetailVO getOneOrderId(String orderId) {
	
			CarOrderDetailVO carOrderDetailVO = null;
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
	
			try {
	
				con = ds.getConnection();
				pstmt = con.prepareStatement(GET_ONE_ORDERID);
				
				pstmt.setString(1, orderId);
				
				rs = pstmt.executeQuery();
	
				while (rs.next()) {
					// empVo 也稱為 Domain objects
					carOrderDetailVO = new CarOrderDetailVO();
					carOrderDetailVO.setOrderDetailId(rs.getString("order_detail_id"));
					carOrderDetailVO.setOrderId(rs.getString("order_id"));
					carOrderDetailVO.setCreateTime(rs.getTimestamp("create_time"));
					carOrderDetailVO.setCloseTime(rs.getTimestamp("close_time"));
					carOrderDetailVO.setStatus(rs.getString("status"));
					carOrderDetailVO.setRiderPathId(rs.getString("rider_path_id"));
					carOrderDetailVO.setFee(rs.getInt("fee"));
					carOrderDetailVO.setMemberId(rs.getString("member_id"));
					carOrderDetailVO.setCarPoolType(rs.getString("car_pool_type"));
					carOrderDetailVO.setDriver_scores(rs.getInt("driver_scores"));
					carOrderDetailVO.setRider_scores(rs.getInt("rider_scores"));
				}
	
				// Handle any driver errors
			} catch (SQLException se) {
				throw new RuntimeException("A database error occured. "
						+ se.getMessage());
				// Clean up JDBC resources
			} finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException se) {
						se.printStackTrace(System.err);
					}
				}
				if (con != null) {
					try {
						con.close();
					} catch (Exception e) {
						e.printStackTrace(System.err);
					}
				}
			}
			return carOrderDetailVO;
		}
}